using System;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Data;
using System.Data.Common;
using Emlyn.Common.Data.Configuration;

namespace Emlyn.Common.Data.SqlServer.SqlDBAccess
{
    public class SimpleSQL
    {
        public static SqlDataReader GetReader(string aConnectionString, string aSQL)
        {
            return GetReader(aConnectionString, aSQL, null, null);
        }

        public static SqlDataReader GetReader(string aSQL)
        {
            return GetReader(Config.GetConnectionString(), aSQL, null, null);
        }

        public static SqlDataReader GetReader(string aConnectionString, string aSQL, List<SqlParameter> aParams)
        {
            return GetReader(aConnectionString, aSQL, aParams, null);
        }

        public static SqlDataReader GetReader(string aSQL, List<SqlParameter> aParams)
        {
            return GetReader(Config.GetConnectionString(), aSQL, aParams, null);
        }

        public static SqlDataReader GetReader(string aConnectionString, string aSQL, List<SqlParameter> aParams, SimpleSQLProblemHandler aProblemHandler)
        {
            bool lDoTry = true;
            int lTryNumber = 1;
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader retval = null;

            if (aProblemHandler == null)
                aProblemHandler = new SimpleSQLGenericProblemHandler();

            while (lDoTry)
            {
                try
                {
                    conn = new SqlConnection(aConnectionString);
                    conn.Open();

                    cmd = new SqlCommand(aSQL, conn);

                    if (aParams != null)
                    {
                        foreach (SqlParameter lParam in aParams)
                        {
                            cmd.Parameters.Add(lParam);
                        }
                    }

                    retval = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

                    lDoTry = false;
                }
                catch (Exception ex)
                {
                    if (conn != null)
                        conn.Close();

                    aProblemHandler.SimpleSQLException(ex, lTryNumber, ref lDoTry);

                    if (!lDoTry)
                        throw ex;
                    else
                        lTryNumber++;
                }
            }

            if (lTryNumber > 1)
            {
                // we've had retries, so let the problem handler 
                // know that we were successful.
                aProblemHandler.SimpleSQLSuccess(lTryNumber);
            }

            return retval;
        }

        public static SqlDataReader GetReader(string aSQL, List<SqlParameter> aParams, SimpleSQLProblemHandler aProblemHandler)
        {
            return GetReader(Config.GetConnectionString(), aSQL, aParams, aProblemHandler);
        }

        public static DataSet GetDataSet(string aConnectionString, string aSQL)
        {
            return GetDataSet(aConnectionString, aSQL, null);
        }

        public static DataSet GetDataSet(string aSQL)
        {
            return GetDataSet(Config.GetConnectionString(), aSQL, null);
        }

        public static DataSet GetDataSet(string aConnectionString, string aSQL, List<SqlParameter> aParams)
        {
            SqlConnection conn = null;
            SqlDataAdapter adapter = null;
            DataSet retval = null;

            conn = new SqlConnection(aConnectionString);

            adapter = new SqlDataAdapter(aSQL, conn);

            if (aParams != null)
            {
                foreach (SqlParameter lParam in aParams)
                {
                    adapter.SelectCommand.Parameters.Add(lParam);
                }
            }

            retval = new DataSet();

            adapter.Fill(retval);

            return retval;
        }

        public static DataSet GetDataSet(string aSQL, List<SqlParameter> aParams)
        {
            return GetDataSet(Config.GetConnectionString(), aSQL, aParams);
        }

        public static int Execute(string aConnectionString, string aSQL)
        {
            return Execute(aConnectionString, aSQL, null, null);
        }

        public static int Execute(string aSQL)
        {
            return Execute(Config.GetConnectionString(), aSQL, null, null);
        }

        public static int Execute(string aConnectionString, string aSQL, List<SqlParameter> aParams)
        {
            return Execute(aConnectionString, aSQL, aParams, null);
        }

        public static int Execute(string aSQL, List<SqlParameter> aParams)
        {
            return Execute(Config.GetConnectionString(), aSQL, aParams, null);
        }

        public static int Execute(string aConnectionString, string aSQL, List<SqlParameter> aParams, SimpleSQLProblemHandler aProblemHandler)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            int retval = -1;
            bool lDoTry = true;
            int lTryNumber = 1;

            if (aProblemHandler == null)
                aProblemHandler = new SimpleSQLGenericProblemHandler();

            while (lDoTry)
            {
                try
                {
                    using (conn = new SqlConnection(aConnectionString))
                    {
                        conn.Open();

                        cmd = new SqlCommand(aSQL, conn);

                        if (aParams != null)
                        {
                            foreach (SqlParameter lParam in aParams)
                            {
                                cmd.Parameters.Add(lParam);
                            }
                        }

                        retval = cmd.ExecuteNonQuery();
                    }

                    lDoTry = false;
                }
                catch (Exception ex)
                {
                    aProblemHandler.SimpleSQLException(ex, lTryNumber, ref lDoTry);

                    if (!lDoTry)
                        throw ex;
                    else
                        lTryNumber++;
                }
            }

            if (lTryNumber > 1)
            {
                // we've had retries, so let the problem handler 
                // know that we were successful.
                aProblemHandler.SimpleSQLSuccess(lTryNumber);
            }

            return retval;
        }

        public static int Execute(string aSQL, List<SqlParameter> aParams, SimpleSQLProblemHandler aProblemHandler)
        {
            return Execute(Config.GetConnectionString(), aSQL, aParams, aProblemHandler);
        }

        /// <summary>
        /// This routine does nothing if successful, throws exceptions
        /// on failure.
        /// </summary>
        /// <param name="aConnectionString"></param>
        public static void TestConnection(string aConnectionString)
        {
            SqlConnection conn = null;

            using (conn = new SqlConnection(aConnectionString))
            {
                conn.Open();
            }
        }

        public static void TestConnection()
        {
            TestConnection(Config.GetConnectionString());
        }

        public static StringCollection GetUserTables(string aConnectionString)
        {
            StringCollection retval = new StringCollection();

            string strSQL =
                "SELECT [name] from sysobjects where xtype='U'";

            using (SqlDataReader reader = GetReader(aConnectionString, strSQL))
            {
                while (reader.Read())
                {
                    if (!reader["Name"].ToString().Equals("dtproperties"))
                        retval.Add(Convert.ToString(reader["Name"]));
                }
            }

            return retval;
        }

        public static StringCollection GetUserTables()
        {
            return GetUserTables(Config.GetConnectionString());
        }

        public static bool TableExists(string aConnectionString, string aTableName)
        {
            bool retval = false;

            string strSQL =
                "SELECT 1 from sysobjects where xtype='U' and Name=@Name";

            SqlParameter lparam = new SqlParameter("@Name", aTableName);
            List<SqlParameter> lparams = new List<SqlParameter>();
            lparams.Add(lparam);

            using (SqlDataReader reader = GetReader(aConnectionString, strSQL, lparams))
            {
                retval = reader.Read();
            }

            return retval;
        }

        public static bool TableExists(string aTableName)
        {
            return TableExists(Config.GetConnectionString(), aTableName);
        }

        public static StringCollection GetColumnsForTable(string aConnectionString, string aTableName)
        {
            StringCollection retval = new StringCollection();

            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader reader = null;

            using (conn = new SqlConnection(aConnectionString))
            {
                conn.Open();

                cmd = new SqlCommand("select * from [" + aTableName + "]", conn);
                reader = cmd.ExecuteReader(System.Data.CommandBehavior.SchemaOnly);

                System.Int32 ColumnCount = reader.FieldCount;

                for (int t = 0; t < ColumnCount; t++)
                {
                    retval.Add(Convert.ToString(reader.GetName(t)));
                }
            }

            return retval;
        }

        public static StringCollection GetColumnsForTable(string aTableName)
        {
            return GetColumnsForTable(Config.GetConnectionString(), aTableName);
        }

        public static System.Data.DataTable GetSchemaTable(string aConnectionString, string aTableName)
        {
            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataReader reader = null;
            System.Data.DataTable retval = null;

            using (conn = new SqlConnection(aConnectionString))
            {
                conn.Open();

                cmd = new SqlCommand("select * from [" + aTableName + "]", conn);
                reader = cmd.ExecuteReader(System.Data.CommandBehavior.SchemaOnly);

                retval = reader.GetSchemaTable();
            }

            return retval;
        }

        public static System.Data.DataTable GetSchemaTable(string aTableName)
        {
            return GetSchemaTable(Config.GetConnectionString(), aTableName);
        }

        /// <summary>
        /// This routine turns a given string into the valid representation
        /// for including in a SQL statement. It doubles all single quotes and 
        /// wraps the string in single quotes.
        /// </summary>
        /// <param name="aValue">Any string</param>
        /// <returns>Valid SQL representation of aValue</returns>
        public static string ToSQLString(string aValue)
        {
            if (aValue != null)
                return "'" + aValue.Replace("'", "''") + "'";
            else
                return "''";
        }

        /// <summary>
        /// This routine turns a given date into the valid representation
        /// for including in a SQL statement. It uses ToOADate()-2 which seems to 
        /// work, but a better representation is desirable!
        /// </summary>
        /// <param name="aValue">Any string</param>
        /// <returns>Valid SQL representation of aValue</returns>
        public static string ToSQLDate(DateTime aValue)
        {
            return Convert.ToString(aValue.ToOADate() - 2);
        }

        public static object nz(object a, object b)
        {
            if (Convert.IsDBNull(a))
                return b;
            else
                return a;
        }
    }
}
